<?php

function FlushCustomer ($TableName, $tmp) {
	global $DBConnection;

	if ($tmp) $TEMPORARY = "TEMPORARY";
       
	//$SQL = "DROP TABLE IF EXISTS `$TableName`;";
	//$DBConnection->dbc->query($SQL);

	$SQL= "CREATE $TEMPORARY TABLE `$TableName` (
			  `cucode` varchar(20) NOT NULL default '',
			  `cunama` varchar(50) default NULL,
			  `top` int(4) default NULL

			  )";
	$DBConnection->dbc->query($SQL);
    $SQL = "INSERT INTO $DBName.`$TableName` SELECT cucode,cuname,top FROM tbldebtur ";
    $DBConnection->dbc->query($SQL);
        

	return true;
}

function GridListHutang () {
		 	global $DBConnection;
			$TEMP = "TEMPORARY";

		 	//FlushCustomer ("aiscustomer", 1);
			//$current_date = date("Y-m-d",mktime (0,0,0,GetParam("m",""),1,GetParam("y","")));
			
			list($day,$month,$year) = explode("/",GetParam("agingDate",""));
			$current_date = "$year-$month-$day";

			//$SQL = "DROP TABLE IF EXISTS `ais_hutang_terhutang`";
			//$DBConnection->dbc->query($SQL);

			$SQL = "
					CREATE $TEMP TABLE IF NOT EXISTS `ais_hutang_terhutang` (
					  `ktrasl` varchar(5) default NULL,
					  `tglbkt` date,
					  `nobkt` varchar(16) default NULL,	
                      `nobkta` varchar(16) default NULL,
					  `debtur` varchar(16) default NULL,
					  `debnam` varchar(32) default NULL,
					  `uraian` text default NULL,
					  `curr` varchar(4) default NULL,
					  `skada` varchar(8) default NULL,
					  `jumlah` decimal(15,3) default 0,
					  `rupiah` decimal(15,3) default 0,
					  `cr_term` tinyint(4)default NULL,
					  `aging` varchar(4)default NULL
					) TYPE=MyISAM;
				   ";
			$DBConnection->dbc->query($SQL);

						
			$SQL = "
					INSERT INTO ais_hutang_terhutang
					SELECT ais_hutang.KTRASL, TGLBKT, NOBKT,NOBKTA, DEBTUR, DEBNAM, URAIAN, CURR, SKADA, sum(JUMLAH), sum(RUPIAH), TOP, 
                    IF('$current_date'<=ADDDATE( tglbkt,INTERVAL tbldebtur.top DAY ),'0',
                    IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+tbldebtur.top DAY ),'30',
                    IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+tbldebtur.top DAY ),'60','60g')))
					FROM ais_hutang
					LEFT JOIN tbldebtur ON ais_hutang.DEBTUR=tbldebtur.cucode
					WHERE srt='1' AND ais_hutang.tglbkt<='$current_date' ";
                                        
                    if (GetParam("office",""))   $SQL .= " AND ais_hutang.ktrasl = '".GetParam("office","")."' ";
                    if (GetParam("curr",""))   $SQL .= " AND ais_hutang.curr = '".GetParam("curr","")."' ";
                    if (GetParam("debturCode","")) $SQL .=" AND tbldebtur.cucode  = '".GetParam("debturCode","")."' ";
                    $SQL .= " GROUP BY  nobkt,skada  ";
                    $SQL .= " order by nobkt";
                    //die($SQL);
                        //print $SQL;die();

			$DBConnection->dbc->query($SQL);

			//$SQL = "DROP TABLE IF EXISTS `ais_hutang_terbayar`";
			//$DBConnection->dbc->query($SQL);

			$SQL = "
					CREATE $TEMP TABLE IF NOT EXISTS `ais_hutang_terbayar` (
					  `nobkt` varchar(16) default NULL,					  
					  `nobkta` varchar(16) default NULL,
                      `bayar` varchar(32) default NULL
					) TYPE=MyISAM;
				   ";
			$DBConnection->dbc->query($SQL);

			$SQL = "
					INSERT INTO ais_hutang_terbayar
					SELECT NOBKT, NOBKTA, SUM(JUMLAH) 
					FROM ais_hutang
                    LEFT JOIN tbldebtur ON ais_hutang.DEBTUR=tbldebtur.cucode
					WHERE srt='2' AND ais_hutang.tglbkt<='$current_date' ";
                    if (GetParam("office",""))   $SQL .= " AND ais_hutang.ktrasl = '".GetParam("office","")."' ";
                    if (GetParam("curr",""))   $SQL .= " AND ais_hutang.curr = '".GetParam("curr","")."' ";
                    if (GetParam("debturCode","")) $SQL .=" AND tbldebtur.cucode  = '".GetParam("debturCode","")."' ";
                                        
                        $SQL .= " group by nobkta order by nobkta";
                        
			//print $SQL;die();
			$DBConnection->dbc->query($SQL);


			//$SQL = "DROP TABLE IF EXISTS `ais_hutang_remain`";
			//$DBConnection->dbc->query($SQL);

			$SQL = "
					CREATE $TEMP TABLE IF NOT EXISTS `ais_hutang_remain` (
					  `ktrasl` varchar(5) default NULL,
					  `tglbkt` date,
					  `nobkt` varchar(16) default NULL,
					  `debtur` varchar(16) default NULL,
					  `debnam` varchar(32) default NULL,
					  `uraian` text default NULL,
					  `curr` varchar(4) default NULL,
					  `skada` varchar(8) default NULL,
                      `cr_term` tinyint(4)default NULL,
					  `jumlahcurr` varchar(32)default NULL,
					  `jumlah30` varchar(32)default NULL,
					  `jumlah60` varchar(32)default NULL,
					  `jumlah60g` varchar(32)default NULL,
					  `rupiah` decimal(15,2)default 0
					  
					) TYPE=MyISAM;
				   ";
			$DBConnection->dbc->query($SQL);


                        $SQL = "
                                INSERT INTO ais_hutang_remain
                                SELECT 
                                ais_hutang_terhutang.ktrasl,
                                ais_hutang_terhutang.tglbkt,
                                ais_hutang_terhutang.nobkt,
                                ais_hutang_terhutang.debtur,
                                ais_hutang_terhutang.debnam,
                                ais_hutang_terhutang.uraian,
                                ais_hutang_terhutang.curr,
                                ais_hutang_terhutang.skada,
                                ifnull(ais_hutang_terhutang.cr_term, 0) AS top,
                                if(ais_hutang_terhutang.aging='0', ais_hutang_terhutang.jumlah-ifnull(ais_hutang_terbayar.bayar,0) ,0) as jumlahcurr,
                                if(ais_hutang_terhutang.aging='30', ais_hutang_terhutang.jumlah-ifnull(ais_hutang_terbayar.bayar,0) ,0) as jumlah30,
                                if(ais_hutang_terhutang.aging='60', ais_hutang_terhutang.jumlah-ifnull(ais_hutang_terbayar.bayar,0) ,0) as jumlah60,
                                if(ais_hutang_terhutang.aging='60g', ais_hutang_terhutang.jumlah-ifnull(ais_hutang_terbayar.bayar,0) ,0) as jumlah90,
                                ais_hutang_terhutang.rupiah
                                FROM
                                ais_hutang_terhutang
                                LEFT JOIN ais_hutang_terbayar ON (ais_hutang_terhutang.nobkt = ais_hutang_terbayar.nobkta)
                                WHERE
                                  (ifnull(ais_hutang_terbayar.nobkt, '') = '') OR (ais_hutang_terhutang.jumlah-ais_hutang_terbayar.bayar) <> 0
                                ORDER BY debtur,nobkt
                        ";
								
			$DBConnection->dbc->query($SQL);

                        $SQL = "
					SELECT ktrasl, nobkt, tglbkt, debtur, debnam, uraian, cr_term as top, skada,
					(jumlahcurr+jumlah30+jumlah60+jumlah60g) as total, rupiah
					FROM ais_hutang_remain 
					ORDER BY debtur
			       ";
			return $DBConnection->dbc->get_results($SQL,ARRAY_A);

}

function GridListPiutang () {
		 	global $DBConnection;
			$TEMP = "TEMPORARY";

		 	//FlushCustomer ("aiscustomer", 1);
			//$current_date = date("Y-m-d",mktime (0,0,0,GetParam("m",""),1,GetParam("y","")));

			list($day,$month,$year) = explode("/",GetParam("agingDate",""));
			$current_date = "$year-$month-$day";

			//$SQL = "DROP TABLE IF EXISTS `ais_piutang_terhutang`";
			//$DBConnection->dbc->query($SQL);

			$SQL = "
					CREATE $TEMP TABLE `ais_piutang_terhutang` (
					  `ktrasl` varchar(5) default NULL,
					  `tglbkt` date,
					  `nobkt` varchar(16) default NULL,	
                      `nobkta` varchar(16) default NULL,
					  `debtur` varchar(16) default NULL,
					  `debnam` varchar(32) default NULL,
					  `uraian` text default NULL,
					  `curr` varchar(4) default NULL,
					  `skada` varchar(8) default NULL,
					  `jumlah` decimal(15,3) default 0,
					  `rupiah` decimal(15,3) default 0,
					  `cr_term` tinyint(4)default NULL,
					  `aging` varchar(4)default NULL
					) TYPE=MyISAM;
				   ";
			$DBConnection->dbc->query($SQL);

			$SQL = "
					INSERT INTO ais_piutang_terhutang
					SELECT KTRASL, TGLBKT, NOBKT,NOBKTA, DEBTUR, DEBNAM, URAIAN, CURR, SKADA, JUMLAH, RUPIAH, TOP, 
          IF('$current_date'<=ADDDATE( tglbkt,INTERVAL tbldebtur.top DAY ),'0',
          IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+tbldebtur.top DAY ),'30',
          IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+tbldebtur.top DAY ),'60','60g')))
					FROM ais_piutang
					LEFT JOIN tbldebtur ON ais_piutang.DEBTUR=tbldebtur.cucode
					WHERE srt='1' AND ais_piutang.tglbkt<='$current_date' ";
                                        
          if (GetParam("office",""))   $SQL .= " AND ais_piutang.ktrasl = '".GetParam("office","")."' ";
          if (GetParam("curr",""))   $SQL .= " AND ais_piutang.curr = '".GetParam("curr","")."' ";
          if (GetParam("debturCode","")) $SQL .=" AND tbldebtur.cucode  = '".GetParam("debturCode","")."' ";
          $SQL .= " order by nobkt";

                        //print $SQL;die();

			$DBConnection->dbc->query($SQL);

			//$SQL = "DROP TABLE IF EXISTS `ais_piutang_terbayar`";
			//$DBConnection->dbc->query($SQL);

			$SQL = "
					CREATE $TEMP TABLE `ais_piutang_terbayar` (
					  `nobkt` varchar(16) default NULL,					  
					  `nobkta` varchar(16) default NULL,
                      `bayar` varchar(32) default NULL
					) TYPE=MyISAM;
				   ";
			$DBConnection->dbc->query($SQL);

			$SQL = "
					INSERT INTO ais_piutang_terbayar
					SELECT NOBKT, NOBKTA, SUM(JUMLAH) 
					FROM ais_piutang
          LEFT JOIN tbldebtur ON ais_piutang.DEBTUR=tbldebtur.cucode
					WHERE srt='2' AND ais_piutang.tglbkt<='$current_date' ";
          if (GetParam("office",""))   $SQL .= " AND ais_piutang.ktrasl = '".GetParam("office","")."' ";
          if (GetParam("curr",""))   $SQL .= " AND ais_piutang.curr = '".GetParam("curr","")."' ";                        
          if (GetParam("debturCode","")) $SQL .=" AND tbldebtur.cucode  = '".GetParam("debturCode","")."' ";
                                        
          $SQL .= " group by nobkta order by nobkta";
                        //print $SQL;die();
			$DBConnection->dbc->query($SQL);


			//$SQL = "DROP TABLE IF EXISTS `ais_piutang_remain`";
			//$DBConnection->dbc->query($SQL);

			$SQL = "
					CREATE $TEMP TABLE `ais_piutang_remain` (
					  `ktrasl` varchar(5) default NULL,
					  `tglbkt` date,
					  `nobkt` varchar(16) default NULL,
					  `debtur` varchar(16) default NULL,
					  `debnam` varchar(32) default NULL,
					  `uraian` text default NULL,
					  `curr` varchar(4) default NULL,
					  `skada` varchar(8) default NULL,
                      `cr_term` tinyint(4) default NULL,
					  `jumlahcurr` varchar(32) default NULL,
					  `jumlah30` varchar(32) default NULL,
					  `jumlah60` varchar(32) default NULL,
					  `jumlah60g` varchar(32) default NULL,
					  `rupiah` decimal(15,3) default 0
					) TYPE=MyISAM;
				   ";
			$DBConnection->dbc->query($SQL);


                        $SQL = "
                                        INSERT INTO ais_piutang_remain
                                        SELECT 
                                        ais_piutang_terhutang.ktrasl,
                                        ais_piutang_terhutang.tglbkt,
                                        ais_piutang_terhutang.nobkt,
                                        ais_piutang_terhutang.debtur,
                                        ais_piutang_terhutang.debnam,
                                        ais_piutang_terhutang.uraian,
                                        ais_piutang_terhutang.curr,
                                        ais_piutang_terhutang.skada,
                                        ifnull(ais_piutang_terhutang.cr_term, 0) AS top,
                                        if(ais_piutang_terhutang.aging='0', ais_piutang_terhutang.jumlah-ifnull(ais_piutang_terbayar.bayar,0) ,0) as jumlahcurr,
                                        if(ais_piutang_terhutang.aging='30', ais_piutang_terhutang.jumlah-ifnull(ais_piutang_terbayar.bayar,0) ,0) as jumlah30,
                                        if(ais_piutang_terhutang.aging='60', ais_piutang_terhutang.jumlah-ifnull(ais_piutang_terbayar.bayar,0) ,0) as jumlah60,
                                        if(ais_piutang_terhutang.aging='60g', ais_piutang_terhutang.jumlah-ifnull(ais_piutang_terbayar.bayar,0) ,0) as jumlah90,
                                        ais_piutang_terhutang.rupiah
                                        FROM
                                        ais_piutang_terhutang
                                        LEFT OUTER JOIN ais_piutang_terbayar ON (ais_piutang_terhutang.nobkt = ais_piutang_terbayar.nobkta)
                                        WHERE
                                          (ifnull(ais_piutang_terbayar.nobkt, '') = '') OR (ais_piutang_terhutang.jumlah-ais_piutang_terbayar.bayar) <> 0
                                        ORDER BY debtur,nobkt
                        ";
			$DBConnection->dbc->query($SQL);

                   $SQL = "
					SELECT ktrasl, nobkt, tglbkt, debtur, debnam, uraian, cr_term as top, skada,
					(jumlahcurr+jumlah30+jumlah60+jumlah60g) as total, rupiah
					FROM ais_piutang_remain 
				   ";
				   $SQL .= "
					ORDER BY debtur
			       ";
			return $DBConnection->dbc->get_results($SQL,ARRAY_A);

}

$hutang = GridListHutang();
$piutang = GridListPiutang();
$total = 0;
?>
<html>
 <head>
 	<title>SOA Summary</title>
 	<style>
 		body,table{
 			font-family: Verdana,Arial,Sans;
 			font-size: 12px
		}
 	</style>
 </head>
<body>

</body>
</html>
<table border="1">
    <tr>
        <th>Reff</th>
        <th>Date</th>
        <th>Description</th>
        <th>Amount (ORG)</th>
        <th>D/C</th>
        <th>Amount (RPH)</th>
        <th>Account</th>
        <th>Reference</th>
    </tr>
    <? if($hutang): ?>
    <? foreach($hutang as $row): ?>
    <tr>
    	<td><?=$row['nobkt']?></td>
    	<td><?=$row['tglbkt']?></td>
    	<td><?=$row['uraian']?></td>
    	<td><?=$row['total']?></td>
    	<td>Cr</td>
    	<td><?
    			$total -= floor($row['rupiah']);
    			echo floor($row['rupiah'])
    		?></td>
    	<td><?=$row['skada']?></td>
    	<td><?=$row['ktrasl']?></td>
    </tr>
    <? endforeach; ?>
    <? endif; ?>
    <? if($piutang): ?>
    <? foreach($piutang as $row): ?>
    <tr>
    	<td><?=$row['nobkt']?></td>
    	<td><?=$row['tglbkt']?></td>
    	<td><?=$row['uraian']?></td>
    	<td><?=$row['total']?></td>
    	<td>Dr</td>
    	<td><?
    			$total += floor($row['rupiah']);
    			echo floor($row['rupiah'])
    		?></td>
    	<td><?=$row['skada']?></td>
    	<td><?=$row['ktrasl']?></td>
    </tr>
    <? endforeach; ?>
    <? endif; ?>
    <tr>
    	<td>&nbsp;</td>
    	<td>&nbsp;</td>
    	<td>&nbsp;</td>
    	<td><?=$total?></td>
    	<td>&nbsp;</td>
    	<td><?=$total?></td>
    	<td>&nbsp;</td>
    	<td>&nbsp;</td>
    </tr>
</table>
